﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WinStudentMS
{
    public partial class GradeListForm : Form
    {
        public GradeListForm()
        {
            InitializeComponent();
        }

        private int flag = 0;
        private int editGradeId = 0;
        private string oldGradeName = "";

        private void GradeListForm_Load(object sender, EventArgs e)
        {
            txtGradeName.Text = "";
            flag = 0;
            btnSubmit.Text = "添加";
            LoadGradeList();
        }

        private void LoadGradeList()
        {
            string sql = "select GradeId,GradeName from GradeInfo";
            DataTable dataTable = SqlHelper.GetDataTable(sql);
            dgvGradeList.DataSource = dataTable;
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            if (flag != 0)
            {
                flag = 0;
                btnSubmit.Text = "添加";
                txtGradeName.Text = "";
            }
        }

        private void dgvGradeList_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex > -1)
            {
                DataGridViewCell cell = dgvGradeList.Rows[e.RowIndex].Cells[e.ColumnIndex];
                DataRow dataRow = (dgvGradeList.Rows[e.RowIndex].DataBoundItem as DataRowView).Row;
                editGradeId = (int)dataRow["GradeId"];
                if (cell is DataGridViewLinkCell && cell.FormattedValue.ToString() == "修改")
                {
                    txtGradeName.Text = dataRow["GradeName"].ToString();
                    oldGradeName = txtGradeName.Text;
                    flag = 1;
                    btnSubmit.Text = "修改";
                }
                else if (cell is DataGridViewLinkCell && cell.FormattedValue.ToString() == "删除")
                {
                    if (MessageBox.Show("您确定要删除该年级及其相关班级和学生的信息吗？", "删除年级提示", MessageBoxButtons.YesNo,
                    MessageBoxIcon.Question) == DialogResult.Yes)
                    {
                        string delStudent = "delete from StudentInfo where ClassId in (select ClassId from ClassInfo where GradeId=@GradeId)";
                        string delClass = "delete from ClassInfo where GradeId=@GradeId";
                        string delGrade = "delete from GradeInfo where GradeId=@GradeId";
                        SqlParameter[] para =
                        {
                            new SqlParameter("@GradeId", editGradeId)
                        };
                        List<CommandInfo> listComs = new List<CommandInfo>();
                        CommandInfo comStudent = new CommandInfo()
                        {
                            CommandText = delStudent,
                            IsProc = false,
                            Parameters = para
                        };
                        listComs.Add(comStudent);
                        CommandInfo comClass = new CommandInfo()
                        {
                            CommandText = delClass,
                            IsProc = false,
                            Parameters = para
                        };
                        listComs.Add(comClass);
                        CommandInfo comGrade = new CommandInfo()
                        {
                            CommandText = delGrade,
                            IsProc = false,
                            Parameters = para
                        };
                        listComs.Add(comGrade);
                        bool result = SqlHelper.ExecuteTrans(listComs);
                        if (result)
                        {
                            MessageBox.Show("该年级信息删除成功!", "删除年级提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            DataTable dataTable = (DataTable)dgvGradeList.DataSource;
                            dataTable.Rows.Remove(dataRow);
                            dgvGradeList.DataSource = dataTable;
                        }
                        else
                        {
                            MessageBox.Show("该年级信息删除成功!", "删除年级提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }
                }
            }
        }

        private void btnSubmit_Click(object sender, EventArgs e)
        {
            string gradeName = txtGradeName.Text.Trim();
            if (string.IsNullOrEmpty(gradeName))
            {
                MessageBox.Show("年级名称不能为空！", "添加年级提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (flag == 0)
            {
                string sqlExist = "select count(1) from GradeInfo where GradeName=@GradeName";
                SqlParameter paraName = new SqlParameter("@GradeName", gradeName);
                object obj = SqlHelper.ExecuteScalar(sqlExist, paraName);
                if (obj != null && ((int)obj) > 0)
                {
                    MessageBox.Show("年级名称已存在！", "添加年级提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                //添加入库，并返回年级编号
                string sqlAdd = "insert into GradeInfo(GradeName) values(@GradeName);select @@Identity";
                SqlParameter paraNameNew = new SqlParameter("@GradeName", gradeName);
                object objGradeId = SqlHelper.ExecuteScalar(sqlAdd, paraNameNew);
                if (objGradeId != null && int.Parse(objGradeId.ToString()) > 0)
                {
                    MessageBox.Show($"年级：{gradeName}添加成功", "添加年级提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    DataTable dataTable = (DataTable)dgvGradeList.DataSource;
                    DataRow dataRow = dataTable.NewRow();
                    dataRow["GradeId"] = int.Parse(objGradeId.ToString());
                    dataRow["GradeName"] = gradeName;
                    dataTable.Rows.Add(dataRow);
                    dgvGradeList.DataSource = dataTable;
                }
                else
                {
                    MessageBox.Show($"年级：{gradeName}添加失败", "添加年级提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
            }
            else if (flag == 1)
            {
                if (gradeName == oldGradeName)
                {
                    MessageBox.Show($"年级名称{gradeName}并未修改！", "修改年级提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                string sqlExist = "select count(1) from GradeInfo where GradeName=@GradeName and GradeId<>@GradeId";
                SqlParameter[] paras = 
                {
                    new SqlParameter("@GradeName", gradeName),
                    new SqlParameter("@GradeId", editGradeId)
                };
                object obj = SqlHelper.ExecuteScalar(sqlExist, paras);
                if (obj != null && ((int)obj) > 0)
                {
                    MessageBox.Show("年级名称已存在！", "修改年级提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                string sqlUpdate = "update GradeInfo set GradeName=@GradeName where GradeId=@GradeId";
                //SqlParameter paraNameNew = new SqlParameter("@GradeName", gradeName);
                int count = SqlHelper.ExecuteNonQuery(sqlUpdate, paras);
                if (count > 0)
                {
                    MessageBox.Show($"年级：{gradeName}修改成功", "修改年级提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    DataTable dataTable = (DataTable)dgvGradeList.DataSource;
                    DataRow[] dataRows = dataTable.Select("GradeId=" + editGradeId);
                    DataRow dataRow = dataRows[0];
                    dataRow["GradeName"] = gradeName;
                    dgvGradeList.DataSource = dataTable;
                }
                else
                {
                    MessageBox.Show($"年级：{gradeName}修改失败", "修改年级提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
            }
        }

        private void btnDel_Click(object sender, EventArgs e)
        {
            List<int> listId = new List<int>();
            for (int i = 0; i < dgvGradeList.Rows.Count; i++)
            {
                DataGridViewCheckBoxCell cell = dgvGradeList.Rows[i].Cells["colCheck"] as DataGridViewCheckBoxCell;
                bool check = Convert.ToBoolean(cell.Value);
                if (check)
                {
                    DataRow dr = (dgvGradeList.Rows[i].DataBoundItem as DataRowView).Row;
                    int gradeId = (int)dr["GradeId"];
                    listId.Add(gradeId);
                }
            }
            if (listId.Count == 0)
            {
                MessageBox.Show("请选择删除的年级信息!", "删除年级提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            else
            {
                if (MessageBox.Show("您确定要删除这些年级及其相关的班级、学生的信息吗？", "删除年级提示", MessageBoxButtons.YesNo,
                    MessageBoxIcon.Question) == DialogResult.Yes)
                {

                    string delStudent = "delete from StudentInfo where ClassId in (select ClassId from ClassInfo where GradeId=@GradeId)";
                    string delClass = "delete from ClassInfo where GradeId=@GradeId";
                    string delGrade = "delete from GradeInfo where GradeId=@GradeId";
                    List<CommandInfo> listComs = new List<CommandInfo>();
                    foreach (int id in listId)
                    {
                        SqlParameter[] para =
                        {
                            new SqlParameter("@GradeId", id)
                        };
                        CommandInfo comStudent = new CommandInfo()
                        {
                            CommandText = delStudent,
                            IsProc = false,
                            Parameters = para
                        };
                        listComs.Add(comStudent);
                        CommandInfo comClass = new CommandInfo()
                        {
                            CommandText = delClass,
                            IsProc = false,
                            Parameters = para
                        };
                        listComs.Add(comClass);
                        CommandInfo comGrade = new CommandInfo()
                        {
                            CommandText = delGrade,
                            IsProc = false,
                            Parameters = para
                        };
                        listComs.Add(comGrade);
                    }

                    bool result = SqlHelper.ExecuteTrans(listComs);
                    if (result)
                    {
                        MessageBox.Show("这些年级信息及其相关班级、学生信息删除成功!", "删除年级提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        DataTable dataTable = (DataTable)dgvGradeList.DataSource;
                        string idStr = string.Join(",", listId);
                        DataRow[] dataRow = dataTable.Select("GradeId in(" + idStr + ")");
                        foreach (DataRow row in dataRow)
                        {
                            dataTable.Rows.Remove(row);
                        }
                        dgvGradeList.DataSource = dataTable;
                    }
                    else
                    {
                        MessageBox.Show("这些年级信息删除成功!", "删除年级提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
        }
    }
}
